package com.wangfugui.mybatisplus.task;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.util.StringUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.google.common.collect.Lists;
import com.wangfugui.mybatisplus.domain.*;
import com.wangfugui.mybatisplus.service.*;
import com.wangfugui.mybatisplus.utils.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.Year;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.*;
import java.util.stream.Collectors;

import static com.wangfugui.mybatisplus.utils.EmailWithAttachmentSender.sendEmailWithAttachment;


/**
 * 基金日报相关任务
 */
@Slf4j
@Component
public class FundTestTask {
    private static final String directory = "/usr/local/mail";

    private String forDelete = "For Delete";
    @Autowired
    private SecurityInfoService securityInfoService;
    @Autowired
    private PortfolioInformationService portfolioInformationService;
    @Autowired
    private DailyPnlService dailyPnlService;
    @Autowired
    private TransactionDetailService transactionDetailService;

    @Autowired
    private RestScheduleService restScheduleService;
    @Autowired
    private BeginningPriceService beginningPriceService;
    @Autowired
    private UnrealizedPnlService unrealizedPnlService;
    /**
     * 每天执行一次
     */
//    @Scheduled(cron = "0 0 1 * * ?")
    @Transactional(rollbackFor = Exception.class)
    public void task() throws ParseException {
        long start = System.currentTimeMillis();
        log.info("【sendFundDailyEmailTask】开始任务:发送基金日报邮件......");
        List<TransactionDetail> list = new ArrayList<>();
        //每天定时拉取邮件附件到本地
        downloadAttachments();
        int x = 1/0;
        List<List<Map<Integer, String>>> lists = readAttachmentsDataList();
        lists.forEach(orgData->{
            String reportDate = getReportDate(orgData);
            Date date = null;
            try {
                date = DateUtils.parseDate(reportDate, "yyyy/MM/dd");
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }
            Map<String, SecurityInfo> securityInfoMap = securityInfoService.getSecurityInfoMap();
            List<Transaction> transactionList = getTransactionList(orgData,securityInfoMap);
            saveTransactionDetail(date, transactionList);
            Map<String, List<Transaction>> collect = transactionList.stream().filter(t -> "S".equals(t.getBs()))
                    .collect(Collectors.groupingBy(Transaction::getStockCode));


//            removeDailyPnl(date);
            List<Equity> equityList = null;
            try {
                equityList = getEquityList(orgData,securityInfoMap,collect);
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }
            List<Equity> otherEquityList = getOtherEquityList(date,transactionList,equityList);
            if(!CollectionUtils.isEmpty(otherEquityList)){
                equityList.addAll(otherEquityList);
            }
            List<Equity> saledEquity = getSaledEquity(equityList, date);
            if(!CollectionUtils.isEmpty(saledEquity)){
                equityList.addAll(saledEquity);
            }
            Map<String, List<Equity>> marketMap = equityList.stream().collect(Collectors.groupingBy(e -> e.getMarket()));
            List<Equity> usEquities = marketMap.get("US Market");
            List<Equity> hkEquities = marketMap.get("HK Market");
            List<Equity> otherEquities = marketMap.get("Other Market");
            EquitySummary usEquitySummary = getEquitySummary(usEquities,"Total总计-美股");
            EquitySummary hkEquitySummary = getEquitySummary(hkEquities,"Total总计-港股");
            EquitySummary otherEquitySummary = getEquitySummary(otherEquities,"Total总计-其他市场");
            EquitySummary grandEquitySummary = getEquitySummary(equityList,"Grand Total");
            equityList = wrapEquityList(equityList);
            usEquities = wrapEquityList(usEquities);
            hkEquities = wrapEquityList(hkEquities);
            otherEquities = wrapEquityList(otherEquities);


//            saveUnrealizedPnl(equityList,reportDate);
            List<DailyPnl> dailyPnls = equityListToDailyPnl(equityList);
//            equityList.forEach(dailyPnl -> {
//                if(collect.containsKey(dailyPnl.getTicker())){
//                    System.out.println("有卖出动作"+dailyPnl.getTicker());
//                    List<Transaction> transactions = collect.get(dailyPnl.getTicker());
//                    BigDecimal x = new BigDecimal(0);
//                    transactions.forEach(transaction -> {
//                        String qty = transaction.getQty();
//                        String grossPriceLocalCCY = transaction.getGrossPriceLocalCCY();
//                        if(canConvertToBigDecimal(qty)&&canConvertToBigDecimal(grossPriceLocalCCY)){
//
//                        }
//                    });
//                }
//            });
//            saveDailyPnl(date,dailyPnls);
            //保存到数据库中
//            saveDailyPnl(date,dailyPnls);
        });

        int o = 1/0;
    }

    private void saveTransactionDetail(Date date, List<Transaction> transactionList) {
        LambdaQueryWrapper<TransactionDetail> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(TransactionDetail::getDate,date);
        transactionDetailService.remove(queryWrapper);
        //将transactionList保存到数据库中
        if(!CollectionUtils.isEmpty(transactionList)){
            Date finalDate = date;
            List<TransactionDetail> collect = transactionList.stream().map(e -> {
                TransactionDetail transactionDetail = new TransactionDetail();
                transactionDetail.setStockCode(e.getStockCode());
                transactionDetail.setDate(finalDate);
                transactionDetail.setOperation(e.getBs());
                transactionDetail.setQty(e.getQty());
                transactionDetail.setValue(e.getNetSettlementAmount());
                return transactionDetail;
            }).collect(Collectors.toList());
            transactionDetailService.saveBatch(collect);
        }
    }

    private List<Equity> getSaledEquity(List<Equity> equityList, Date reportDate) {
        //获取当前持仓代码列表
        List<String> codes = equityList.stream().map(Equity::getTicker).collect(Collectors.toList());
        List<Equity> results = new ArrayList<>();
        List<DailyPnl> dailyPnls = dailyPnlService.notHolding(codes, reportDate);
        if(CollectionUtils.isEmpty(dailyPnls)){
            return null;
        }
        Map<String, List<DailyPnl>> collect = dailyPnls.stream().collect(Collectors.groupingBy(dailyPnl -> dailyPnl.getStockCode()));
        collect.forEach((k,v)->{
            DailyPnl dailyPnl = v.get(0);
            Equity equity = new Equity();
            equity.setTicker(dailyPnl.getStockCode());
            equity.setInvestmentName(dailyPnl.getStockName());
            equity.setExchangeName(dailyPnl.getExchangeName());
            equity.setMarket(dailyPnl.getMarket());
            equity.setQuantity("0");
            equity.setUnrealizedPnlTotal("0");
            equity.setMtdPNLUnrealised("0");
            //TODO
            List<DailyPnl> dailyPnls1 = dailyPnlService.listByMonth(dailyPnl.getStockCode(), reportDate);
            List<DailyPnl> dailyPnls2 = dailyPnlService.listByYear(dailyPnl.getStockCode(), reportDate);
            List<DailyPnl> dailyPnls3 = dailyPnlService.list(dailyPnl.getStockCode(), reportDate);
            BigDecimal reduce1 = dailyPnls1.stream().filter(d -> canConvertToBigDecimal(d.getRealizedPnl()))
                    .map(d -> new BigDecimal(d.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal reduce2 = dailyPnls2.stream().filter(d -> canConvertToBigDecimal(d.getRealizedPnl()))
                    .map(d -> new BigDecimal(d.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal reduce3 = dailyPnls3.stream().filter(d -> canConvertToBigDecimal(d.getRealizedPnl()))
                    .map(d -> new BigDecimal(d.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            equity.setMtdPNLRealised(reduce1.toPlainString());
            equity.setYtdPNL(reduce2.toPlainString());
            equity.setItdPNL(reduce3.toPlainString());
            results.add(equity);
        });
        return results ;
    }

    //获取当天买卖的信息
    private List<Equity> getOtherEquityList(Date reportDate, List<Transaction> transactionList, List<Equity> equityList) {
        if(CollectionUtils.isEmpty(transactionList)){
            return null;
        }
        Map<String, List<Transaction>> map = null;
        if(CollectionUtils.isEmpty(equityList)){
            map = transactionList.stream()
                    .collect(Collectors.groupingBy(Transaction::getStockCode));
        }else{
            List<String> collect = equityList.stream().map(e -> e.getTicker()).collect(Collectors.toList());
            map = transactionList.stream().filter(t -> !collect.contains(t.getStockCode()))
                    .collect(Collectors.groupingBy(Transaction::getStockCode));
        }
        List<Equity> otherEquityList = new ArrayList<>();
        map.forEach((k,v)->{
            Transaction transaction = v.get(0);
            ;
            Equity equity = new Equity();
            equity.setTicker(k);
            equity.setInvestmentName(transaction.getName());
            equity.setExchangeName(transaction.getExchangeName());
            equity.setMarket(transaction.getMarket());
            equity.setHolding(false);
            equity.setQuantity("0");
            equity.setUnrealizedPnlTotal("0");
            equity.setUnrealizedDailyPNLUSD("0");
            equity.setMtdPNLUnrealised("0");
            BigDecimal amount = v.stream().map(e -> e.getNetSettlementAmount().replace(",","")).filter(e -> canConvertToBigDecimal(e))
                    .map(e -> new BigDecimal(e))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            equity.setRealizedDailyPNLUSD(amount.toPlainString());
            //TODO
            List<DailyPnl> dailyPnls1 = dailyPnlService.listByMonth(k,reportDate);
            List<DailyPnl> dailyPnls2 = dailyPnlService.listByYear(k, reportDate);
            List<DailyPnl> dailyPnls3 = dailyPnlService.list(k, reportDate);
            BigDecimal reduce1 = dailyPnls1.stream().filter(d -> canConvertToBigDecimal(d.getRealizedPnl()))
                    .map(d -> new BigDecimal(d.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add).add(amount);
            BigDecimal reduce2 = dailyPnls2.stream().filter(d -> canConvertToBigDecimal(d.getRealizedPnl()))
                    .map(d -> new BigDecimal(d.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add).add(amount);
            BigDecimal reduce3 = dailyPnls3.stream().filter(d -> canConvertToBigDecimal(d.getRealizedPnl()))
                    .map(d -> new BigDecimal(d.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add).add(amount);
            equity.setMtdPNLRealised(reduce1.toPlainString());
            equity.setYtdPNL(reduce2.toPlainString());
            equity.setItdPNL(reduce3.toPlainString());
            otherEquityList.add(equity);
            transactionDetailService.tagCalculate(k, reportDate);
        });

        return otherEquityList;
    }

    private void abc() throws ParseException {

        long start = System.currentTimeMillis();
        log.info("【sendFundDailyEmailTask】开始任务:发送基金日报邮件......");
        //每天定时拉取邮件附件到本地
        downloadAttachments();
        //读取邮件附件原始数据
        List<Map<Integer, String>> orgData = readAttachmentsData();
        String reportDate = getReportDate(orgData);
        Date date = DateUtils.parseDate(reportDate, "yyyy/MM/dd");
        Map<String, SecurityInfo> securityInfoMap = securityInfoService.getSecurityInfoMap();
        List<Transaction> transactionList = getTransactionList(orgData,securityInfoMap);
        removeDailyPnl(date);
        List<Equity> equityList = getEquityList(orgData,securityInfoMap,null);
        Map<String, List<Equity>> marketMap = equityList.stream().collect(Collectors.groupingBy(e -> e.getMarket()));
        List<Equity> usEquities = marketMap.get("US Market");
        List<Equity> hkEquities = marketMap.get("HK Market");
        List<Equity> otherEquities = marketMap.get("Other Market");
        EquitySummary usEquitySummary = getEquitySummary(usEquities,"Total总计-美股");
        EquitySummary hkEquitySummary = getEquitySummary(hkEquities,"Total总计-港股");
        EquitySummary otherEquitySummary = getEquitySummary(otherEquities,"Total总计-其他市场");
        EquitySummary grandEquitySummary = getEquitySummary(equityList,"Grand Total");
        equityList = wrapEquityList(equityList);
        usEquities = wrapEquityList(usEquities);
        hkEquities = wrapEquityList(hkEquities);
        otherEquities = wrapEquityList(otherEquities);


        saveUnrealizedPnl(equityList,reportDate);
        List<DailyPnl> dailyPnls = equityListToDailyPnl(equityList);
        //保存到数据库中
        saveDailyPnl(date,dailyPnls);
        List<Position> positionList = getPositionList(orgData, securityInfoMap, date);
        PositionSummary positionSummary = getPositionSummary(positionList);
        wrap(positionSummary,positionList);
        List<UnrealizedPnl> unrealizedPnls = unrealizedPnlService.listLastTwo(date);
        LambdaQueryWrapper<PortfolioInformation> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(PortfolioInformation::getDate,date);
        portfolioInformationService.remove(wrapper);
        PortfolioInformation portfolioInformation = getPortfolioInformation(date,reportDate,orgData,positionSummary);
        portfolioInformation.setDate(date);
        savePortfolioInformation(portfolioInformation);
//        portfolioInformationService.save(portfolioInformation);
//
        String templateFileName =directory+"/template/template.xlsx";
        String reportFileName = directory+"/reports/Daily_Report" + getReportDate(date) + ".xlsx";
        String finalFileName = directory+"/reports/Sheng_Hong_Daily_Report" + getReportDate(date) + ".xlsx";
//
        try (ExcelWriter excelWriter = EasyExcel.write(reportFileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            Map<String, Object> map = new HashMap<>();
            List<PortfolioInformation> data = new ArrayList<>();
            data.add(portfolioInformation);
            //Portfolio Information  投资组合信息
//            map.put("portfolio", portfolioInformation);
            //TotalUnrealizedPNL总未实现损益
            formatUnrealizedPnl(unrealizedPnls);
            excelWriter.fill(new FillWrapper("pnls", unrealizedPnls),fillConfig, writeSheet);

            formatTransaction(transactionList);
            //Transactions 交易数据
            excelWriter.fill(new FillWrapper("transactions", transactionList),fillConfig, writeSheet);
            //P&L 损益数据
            formatEquity(usEquities);
            formatEquity(hkEquities);
            formatEquity(otherEquities);
            formatEquitySummary(usEquitySummary);
            formatEquitySummary(hkEquitySummary);
            formatEquitySummary(otherEquitySummary);
            formatEquitySummary(grandEquitySummary);

            excelWriter.fill(new FillWrapper("usEquities", usEquities),fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("usEquitiesTotal", Lists.newArrayList(usEquitySummary)),fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("hkEquities", hkEquities),fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("hkEquitiesTotal", Lists.newArrayList(hkEquitySummary)),fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("otherEquities", otherEquities),fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("otherEquitiesTotal", Lists.newArrayList(otherEquitySummary)),fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("grandEquitiesTotal", Lists.newArrayList(grandEquitySummary)),fillConfig, writeSheet);
            formatPositions(positionList    );
            //持仓数据
            excelWriter.fill(new FillWrapper("positions", positionList),fillConfig, writeSheet);
            formatPositionSummary(positionSummary);
            excelWriter.fill(new FillWrapper("positionsSummary", Lists.newArrayList(positionSummary)),fillConfig, writeSheet);
            formatPortfolioInformation(portfolioInformation);
            excelWriter.fill(new FillWrapper("portfolio", Lists.newArrayList(portfolioInformation)),fillConfig, writeSheet);
            excelWriter.fill(map, writeSheet);
        }
        //删除For Delete行
        try {
            int conditionColumnIndex = 0;
            ExcelRowDeleter.deleteRowsByCondition(reportFileName, conditionColumnIndex, forDelete);
            System.out.println("操作完成，满足条件的行已物理删除并覆盖源文件。");
        } catch (IOException e) {
            System.err.println("处理文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
        //格式化,颜色渲染
        ExcelConditionalFormatting.format(reportFileName, finalFileName);
        //发送邮件
        sendEmail(portfolioInformation,finalFileName);
        long end = System.currentTimeMillis();
        System.out.println("【sendFundDailyEmailTask】任务结束，耗时：" + (end - start) / 1000 + "秒");
//        int x = 1/0;
        log.info("【sendFundDailyEmailTask】结束任务:发送基金日报邮件......");

    }
    private void formatPortfolioInformation(PortfolioInformation portfolioInformation) {
        portfolioInformation.setLongExposure(formatNumber(portfolioInformation.getLongExposure())   );
        portfolioInformation.setShortExposure(formatNumber(portfolioInformation.getShortExposure()));
        portfolioInformation.setGrossExposure(formatNumber(portfolioInformation.getGrossExposure()));
        portfolioInformation.setNetExposure(formatNumber(portfolioInformation.getNetExposure()));
        portfolioInformation.setChangeInTotalMarketValue(formatNumber(portfolioInformation.getChangeInTotalMarketValue()));
        portfolioInformation.setAUMAsATPreviousMonthEnd(formatNumber(portfolioInformation.getAUMAsATPreviousMonthEnd()));
        portfolioInformation.setCICCAUMAsAtPreviousDay(formatNumber(portfolioInformation.getCICCAUMAsAtPreviousDay()));
        portfolioInformation.setCICCAUMAsAtReportDay(formatNumber(portfolioInformation.getCICCAUMAsAtReportDay()));
    }

    private void formatPositionSummary(PositionSummary positionSummary) {
        positionSummary.setTotalQuantity(formatNumber(positionSummary.getTotalQuantity()));
        positionSummary.setTotalMarketValueLocalCCY(formatNumber(positionSummary.getTotalMarketValueLocalCCY()));
        positionSummary.setTotalMarketValueUSD(formatNumber(positionSummary.getTotalMarketValueUSD()));
        positionSummary.setLongExposureTotalMarketValueUSD(formatNumber(positionSummary.getLongExposureTotalMarketValueUSD()));
        positionSummary.setShortExposureTotalMarketValueUSD(formatNumber(positionSummary.getShortExposureTotalMarketValueUSD()));
        positionSummary.setGrossExposureTotalMarketValueUSD(formatNumber(positionSummary.getGrossExposureTotalMarketValueUSD()));
        positionSummary.setNetExposureTotalMarketValueUSD(formatNumber(positionSummary.getNetExposureTotalMarketValueUSD()));
    }

    private void formatPositions(List<Position> positionList) {
        positionList.forEach(e -> {
            e.setQuantity(formatNumber(e.getQuantity()));
            e.setCost(formatNumber(e.getCost()));
            e.setMarketPriceLocalCCY(formatNumber(e.getMarketPriceLocalCCY()));
            e.setPriceBeginningOfYear(formatNumber(e.getPriceBeginningOfYear()));
            e.setMarketValueLocalCCY(formatNumber(e.getMarketValueLocalCCY()));
            e.setMarketValueUSD(formatNumber(e.getMarketValueUSD()));
        });
    }

    void formatEquitySummary(EquitySummary summary) {
        summary.setQuantity(formatNumber(summary.getQuantity()));
         summary.setUnrealizedDailyPNLUSD(formatNumber(summary.getUnrealizedDailyPNLUSD()));
         summary.setMtdPNLUnrealised(formatNumber(summary.getMtdPNLUnrealised()));
         summary.setMtdPNLRealised(formatNumber(summary.getMtdPNLRealised()));
         summary.setYtdPNL(formatNumber(summary.getYtdPNL()));
         summary.setItdPNL(formatNumber(summary.getItdPNL()));

    }

    void formatTransaction(List<Transaction> transactionList) {
        transactionList.forEach(e -> {
            e.setQty(formatNumber(e.getQty()));
            e.setGrossPriceLocalCCY(formatNumber(e.getGrossPriceLocalCCY()));
            e.setNetPriceLocalCCY(formatNumber(e.getNetPriceLocalCCY()));
            e.setGrossPriceSettleCCY(formatNumber(e.getGrossPriceSettleCCY()));
            e.setNetPriceSettleCCY(formatNumber(e.getNetPriceSettleCCY()));
            e.setGrossAmountSettleCCY(formatNumber(e.getGrossAmountSettleCCY()));
            e.setNetAmountSettleCCY(formatNumber(e.getNetAmountSettleCCY()));
            e.setCounterpartyFeeCentsPerShare(formatNumber(e.getCounterpartyFeeCentsPerShare()));
            e.setWithholdTaxSettlementCcy(formatNumber(e.getWithholdTaxSettlementCcy()));

        });
    }

    void formatEquity(List<Equity> equityList) {
        equityList.forEach(e -> {
            e.setQuantity(formatNumber(e.getQuantity()));
            e.setUnrealizedDailyPNLUSD((formatNumber(e.getUnrealizedDailyPNLUSD())));
            e.setMtdPNLUnrealised(formatNumber(e.getMtdPNLUnrealised()));
            e.setMtdPNLRealised(formatNumber(e.getMtdPNLRealised()));
            e.setYtdPNL(formatNumber(e.getYtdPNL()));
            e.setItdPNL(formatNumber(e.getItdPNL()));
        });
    }

    void formatUnrealizedPnl(List<UnrealizedPnl> unrealizedPnls) {
        unrealizedPnls.forEach(e -> {
            e.setTotalUnrealizedPnl(formatNumber(e.getTotalUnrealizedPnl()));
        });
    }
    private void removeDailyPnl(Date date) {
        LambdaQueryWrapper<DailyPnl> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(DailyPnl::getDate, date);
        dailyPnlService.remove(wrapper);
    }

    private void savePortfolioInformation(PortfolioInformation portfolioInformation){

        portfolioInformationService.save(portfolioInformation);
    }

    private void saveDailyPnl(Date date, List<DailyPnl> list){

        dailyPnlService.saveBatch(list);
        List<String> codes = list.stream().map(e -> e.getStockCode()).collect(Collectors.toList());
        dailyPnlService.updateHolding(codes);
    }

    private  PortfolioInformation getPortfolioInformation(Date date, String reportDate, List<Map<Integer,String>> list,PositionSummary positionSummary) {
        PortfolioInformation portfolioInformation = new PortfolioInformation();
        portfolioInformation.setLongExposure(positionSummary.getLongExposureTotalMarketValueUSD());
        portfolioInformation.setShortExposure(positionSummary.getShortExposureTotalMarketValueUSD());
        portfolioInformation.setGrossExposure(positionSummary.getGrossExposureTotalMarketValueUSD());
        portfolioInformation.setNetExposure(positionSummary.getNetExposureTotalMarketValueUSD());
        portfolioInformation.setCICCAUMAsAtReportDay(getPortfolioValue(list));
        PortfolioInformation yesTodayInformation = portfolioInformationService.getYesTodayInformation(date);
        TemporalAccessor temporalAccessor = DateTimeFormatter.ofPattern("yyyy/MM/dd").parse(reportDate);
        PortfolioInformation firstOfMonthInformation = portfolioInformationService.getFirstOfMonthInformation(YearMonth.from(temporalAccessor));
        PortfolioInformation firstOfYearInformation = portfolioInformationService.getFirstOfYearInformation(Year.from(temporalAccessor));
        PortfolioInformation firstInformation = portfolioInformationService.getFirstInformation();
        if (yesTodayInformation!=null){
            portfolioInformation.setCICCAUMAsAtPreviousDay(yesTodayInformation.getCICCAUMAsAtReportDay());
        //FU-VR-I-7 (今日 - 昨日)/昨日
            BigDecimal rate = (new BigDecimal(portfolioInformation.getCICCAUMAsAtReportDay())
                    .subtract(new BigDecimal(yesTodayInformation.getCICCAUMAsAtReportDay())))
                    .divide(new BigDecimal(yesTodayInformation.getCICCAUMAsAtReportDay()),4, BigDecimal.ROUND_HALF_UP);
            portfolioInformation.setDailyReturn(rate.multiply(new BigDecimal(100))+"%");
        }
        if (firstOfMonthInformation!=null){
            BigDecimal rate = (new BigDecimal(portfolioInformation.getCICCAUMAsAtReportDay())
                    .subtract(new BigDecimal(firstOfMonthInformation.getCICCAUMAsAtReportDay())))
                    .divide(new BigDecimal(yesTodayInformation.getCICCAUMAsAtReportDay()),4, BigDecimal.ROUND_HALF_UP)
                    ;
            portfolioInformation.setMTDReturn(rate.multiply(new BigDecimal(100))+"%");
        }else {
            portfolioInformation.setMTDReturn("0.00%");
        }
        if (firstOfYearInformation!=null){
            BigDecimal rate = new BigDecimal(portfolioInformation.getCICCAUMAsAtReportDay())
                    .subtract(new BigDecimal(firstOfYearInformation.getCICCAUMAsAtReportDay()))
                    .divide(new BigDecimal(firstOfYearInformation.getCICCAUMAsAtReportDay()),4, BigDecimal.ROUND_HALF_UP)
                    ;
            portfolioInformation.setYTDReturn(rate.multiply(new BigDecimal(100))+"%");
        }
        if (firstInformation!=null){
            BigDecimal rate = new BigDecimal(portfolioInformation.getCICCAUMAsAtReportDay())
                    .subtract(new BigDecimal(firstInformation.getCICCAUMAsAtReportDay()))
                    .divide(new BigDecimal(firstInformation.getCICCAUMAsAtReportDay()),4, BigDecimal.ROUND_HALF_UP);
            portfolioInformation.setITDReturn(rate.multiply(new BigDecimal(100))+"%");
        }
        //持仓H-净敞口与上一日的差值
        String netExposure = yesTodayInformation.getNetExposure();
        String netExposureToday = portfolioInformation.getNetExposure();
        if (StringUtils.isNotBlank(netExposure)&&StringUtils.isNotBlank(netExposureToday)){
            portfolioInformation.setChangeInTotalMarketValue(new BigDecimal(netExposureToday).subtract(new BigDecimal(netExposure)).toPlainString());
        }
        //        FU-VR-I-7 上月底的数值
        PortfolioInformation lastOfLastMonthInformation = portfolioInformationService.getLastOfLastMonthInformation(YearMonth.from(temporalAccessor));
        if (lastOfLastMonthInformation!=null){
            portfolioInformation.setAUMAsATPreviousMonthEnd(lastOfLastMonthInformation.getCICCAUMAsAtReportDay());
        }
        return portfolioInformation;
    }
    private void saveUnrealizedPnl(List<Equity> equityList, String reportDate) {
        Date date = DateUtil.paseDate("yyyy/MM/dd", reportDate);
        LambdaQueryWrapper<UnrealizedPnl> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(UnrealizedPnl::getDate,date);
        unrealizedPnlService.remove(wrapper);
        BigDecimal totalPnl = equityList.stream().filter(e -> canConvertToBigDecimal(e.getUnrealizedDailyPNLUSD()))
                .map(e -> new BigDecimal(e.getUnrealizedDailyPNLUSD()))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        UnrealizedPnl  unrealizedPnl = new UnrealizedPnl();
        unrealizedPnl.setId(0);
        unrealizedPnl.setDate(date);
        unrealizedPnl.setTotalUnrealizedPnl(totalPnl.toPlainString());
        unrealizedPnlService.save(unrealizedPnl);
    }

    private String getReportDate(List<Map<Integer, String>> list){
        Optional<Map<Integer, String>> reportDateOPtional = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).trim().equals("Report Date")).findFirst();
        if (!reportDateOPtional.isPresent()){
            return DateUtil.formatDate("yyyy/MM/dd");
        }
        String reportDateStr = reportDateOPtional.get().get(1);
        return reportDateStr;
    }
    private EquitySummary getEquitySummary(List<Equity> equities, String summaryName) {
        EquitySummary summary = new EquitySummary();
        summary.setName(forDelete);
        if (!CollectionUtils.isEmpty(equities)) {
            summary.setName(summaryName);
            BigDecimal sumQty = equities.stream().filter(e -> canConvertToBigDecimal(e.getQuantity())).map(e -> new BigDecimal(e.getQuantity())).reduce(BigDecimal.ZERO, BigDecimal::add);
            summary.setQuantity(sumQty.toPlainString());
            BigDecimal unrealizedDailyPNLUSD = equities.stream().filter(e -> canConvertToBigDecimal(e.getUnrealizedDailyPNLUSD())).map(e -> new BigDecimal(e.getUnrealizedDailyPNLUSD())).reduce(BigDecimal.ZERO, BigDecimal::add);
            summary.setUnrealizedDailyPNLUSD(unrealizedDailyPNLUSD.toPlainString());
            BigDecimal mtdPNLUnrealised = equities.stream().filter(e -> canConvertToBigDecimal(e.getMtdPNLUnrealised())).map(e -> new BigDecimal(e.getMtdPNLUnrealised())).reduce(BigDecimal.ZERO, BigDecimal::add);
            summary.setMtdPNLUnrealised(mtdPNLUnrealised.toPlainString());
            BigDecimal mtdPNLRealised = equities.stream().filter(e -> canConvertToBigDecimal(e.getMtdPNLRealised())).map(e -> new BigDecimal(e.getMtdPNLRealised())).reduce(BigDecimal.ZERO, BigDecimal::add);
            summary.setMtdPNLRealised(mtdPNLRealised.toPlainString());
            BigDecimal ytdPNL = equities.stream().filter(e -> canConvertToBigDecimal(e.getYtdPNL())).map(e -> new BigDecimal(e.getYtdPNL())).reduce(BigDecimal.ZERO, BigDecimal::add);
            summary.setYtdPNL(ytdPNL.toPlainString());
            BigDecimal itdPNL = equities.stream().filter(e -> canConvertToBigDecimal(e.getItdPNL())).map(e -> new BigDecimal(e.getItdPNL())).reduce(BigDecimal.ZERO, BigDecimal::add);
            summary.setItdPNL(itdPNL.toPlainString());
        }
        return summary;
    }
    private List<Equity> wrapEquityList(List<Equity> equityList) {
        if (CollectionUtils.isEmpty(equityList)){
            Equity equity = new Equity();
            equity.setTicker(forDelete);
            equityList = Lists.newArrayList();
            equityList.add(equity);
            return equityList;
        }
        return equityList;
    }
    private void wrap(PositionSummary summary, List<Position> positionList){
        positionList.stream().filter(x->canConvertToBigDecimal(x.getMarketValueUSD())).forEach(x->{
            BigDecimal multiply = new BigDecimal(x.getMarketValueUSD()).divide(new BigDecimal(summary.getGrossExposureTotalMarketValueUSD()),4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)).setScale(2, RoundingMode.HALF_UP);
            x.setExposureAsAPercentageOfGrossExposure(multiply.toPlainString()+"%");
            x.setExposureAsAPercentageOfGrossExposureValue(multiply);
        });
        String longExposureTotalMarketValueUSD = summary.getLongExposureTotalMarketValueUSD();
        String shortExposureTotalMarketValueUSD = summary.getShortExposureTotalMarketValueUSD();
        String grossExposureTotalMarketValueUSD = summary.getGrossExposureTotalMarketValueUSD();
        BigDecimal longGrossExposure = new BigDecimal(longExposureTotalMarketValueUSD).divide(new BigDecimal(grossExposureTotalMarketValueUSD),4, RoundingMode.HALF_UP)
                .multiply(new BigDecimal(100)).setScale(2, RoundingMode.HALF_UP);

//        BigDecimal longGrossExposure = positionList.stream().filter(p -> new BigDecimal(p.getQuantity()).compareTo(new BigDecimal("0")) > 0)
//                .map(p -> p.getExposureAsAPercentageOfGrossExposureValue())
//                .reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setLongExposureExposureAsAPercentageOfGrossExposure(longGrossExposure.toPlainString()+"%");
//        BigDecimal shortGrossExposure = positionList.stream().filter(p -> canConvertToBigDecimal(p.getExposureAsAPercentageOfGrossExposure()) && new BigDecimal(p.getQuantity()).compareTo(new BigDecimal("0")) < 0)
//                .map(p -> p.getExposureAsAPercentageOfGrossExposureValue())
//                .reduce(BigDecimal.ZERO, BigDecimal::add);
        BigDecimal subtract = longGrossExposure.subtract(new BigDecimal(100));
        summary.setShortExposureExposureAsAPercentageOfGrossExposure(subtract.toPlainString()+"%");
    }

    private PositionSummary getPositionSummary(List<Position> list) {
        PositionSummary summary = new PositionSummary();
        if (CollectionUtils.isEmpty(list)) {
            summary.setTotalName("For Delete");
            summary.setNetExposureName("For Delete");
            summary.setGrossExposureName("For Delete");
            summary.setLongExposureName("For Delete");
            summary.setShortExposureName("For Delete");
            return summary;
        }
        summary.setTotalName("Total总计");
        summary.setLongExposureName("Long Exposure多头敞口");
        summary.setShortExposureName("Short Exposure空头敞口");
        summary.setGrossExposureName("Gross Exposure总敞口");
        summary.setNetExposureName("Net Exposure净敞口");
        BigDecimal totalQuantity = list.stream().filter(p -> canConvertToBigDecimal(p.getQuantity())).map(p -> new BigDecimal(p.getQuantity()))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setTotalQuantity(totalQuantity.toPlainString());
        BigDecimal totalMarketValueLocalCCY = list.stream().filter(p -> canConvertToBigDecimal(p.getMarketValueLocalCCY())).map(p -> new BigDecimal(p.getMarketValueLocalCCY()))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setTotalMarketValueLocalCCY(totalMarketValueLocalCCY.toPlainString());
        BigDecimal totalMarketValueUSD = list.stream().filter(p -> canConvertToBigDecimal(p.getMarketValueUSD())).map(p -> new BigDecimal(p.getMarketValueUSD()))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setTotalMarketValueUSD(totalMarketValueUSD.toPlainString());

        //long exposure
        BigDecimal longExposureMarketValueUSD = list.stream().filter(e -> canConvertToBigDecimal(e.getMarketValueUSD()) && new BigDecimal(e.getQuantity()).compareTo(new BigDecimal("0")) > 0)
                .map(e -> new BigDecimal(e.getMarketValueUSD())).reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setLongExposureTotalMarketValueUSD(longExposureMarketValueUSD.toPlainString());

        //short exposure
        BigDecimal shortMarketValueUSD = list.stream().filter(e -> canConvertToBigDecimal(e.getMarketValueUSD()) && new BigDecimal(e.getQuantity()).compareTo(new BigDecimal("0")) < 0)
                .map(e -> new BigDecimal(e.getMarketValueUSD())).reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setShortExposureTotalMarketValueUSD(shortMarketValueUSD.toPlainString());

        BigDecimal grossExposureMarketValueUSD = list.stream().filter(e -> canConvertToBigDecimal(e.getMarketValueUSD()))
                .map(e -> new BigDecimal(e.getMarketValueUSD()).abs()).reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setGrossExposureTotalMarketValueUSD(grossExposureMarketValueUSD.toPlainString());
        summary.setNetExposureTotalMarketValueUSD(totalMarketValueUSD.toPlainString());

        BigDecimal longExposureMonthNav = list.stream().filter(p -> new BigDecimal(p.getQuantity()).compareTo(new BigDecimal("0")) > 0)
                .map(p -> p.getMarketValueUSDAsAPercentageOfBeginOfMonthNAVValue())
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setLongExposureMarketValueUSDAsAPercentageOfBeginOfMonthNAV(longExposureMonthNav.toPlainString()+"%");

        BigDecimal shortExposureMonthNav = list.stream().filter(p ->  new BigDecimal(p.getQuantity()).compareTo(new BigDecimal("0")) < 0)
                .map(p -> p.getMarketValueUSDAsAPercentageOfBeginOfMonthNAVValue())
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setShortExposureMarketValueUSDAsAPercentageOfBeginOfMonthNAV(shortExposureMonthNav.toPlainString()+"%");

        BigDecimal grossExposureMonthNAV = list.stream().map(p -> p.getMarketValueUSDAsAPercentageOfBeginOfMonthNAVValue().abs())
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        summary.setGrossExposureMarketValueUSDAsAPercentageOfBeginOfMonthNAV(grossExposureMonthNAV.toPlainString()+"%");

        String netMonthNAV = longExposureMonthNav.add(shortExposureMonthNav).toPlainString();
        summary.setNetExposureMarketValueUSDAsAPercentageOfBeginOfMonthNAV(netMonthNAV+"%");

        return summary;
    }
    private List<DailyPnl> equityListToDailyPnl(List<Equity> equityList) {

        List<DailyPnl> collect = equityList.stream().map(e -> {
            DailyPnl dailyPnl = new DailyPnl();
            dailyPnl.setStockCode(e.getTicker());
            dailyPnl.setRealizedPnl(e.getRealizedDailyPNLUSD());
            dailyPnl.setUnrealizedPnl(e.getUnrealizedDailyPNLUSD());
            dailyPnl.setDate(e.getDate());
            dailyPnl.setHolding(e.getHolding());
            dailyPnl.setUnrealizedPnlTotal(e.getUnrealizedPnlTotal());

            return dailyPnl;
        }).collect(Collectors.toList());
        return collect;
    }

    private String getPortfolioValueOfBeginOfMonth(Date reportDate){
        // 将 java.util.Date 转换为 java.time.LocalDate
        LocalDate localDate = new java.sql.Date(reportDate.getTime()).toLocalDate();
        // 转换为 YearMonth
        PortfolioInformation firstOfMonthInformation = portfolioInformationService.getFirstOfMonthInformation(YearMonth.from(localDate));
        if (firstOfMonthInformation == null){
            return null;
        }
        return firstOfMonthInformation.getCICCAUMAsAtReportDay();
    }
    private String getPortfolioValue(List<Map<Integer,String>> list){
        Optional<Map<Integer, String>> baseCurrency =
                list.stream().filter(e -> e.get(0) != null && e.get(0).trim().equals("Base Currency")).findFirst();
        if (!baseCurrency.isPresent()){
            return "";
        }
        String baseCurrencyValue = baseCurrency.get().get(1);
        List<Map<Integer, String>> netAssetValueReport = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).trim().equals("Net Asset Value Report")).collect(Collectors.toList());
        if (netAssetValueReport == null || netAssetValueReport.size() <= 1){
            return "";
        }
        List<Map<Integer, String>> subList = netAssetValueReport.subList(1, netAssetValueReport.size());
        for (Map<Integer, String> map : subList) {
            if (map.get(5).equals(baseCurrencyValue)){
                return map.get(8);
            }
        }
        return "";
    }
    private List<Position> getPositionList(List<Map<Integer,String>> list,Map<String, SecurityInfo> securityInfoMap,Date reportDate){
        List<Map<Integer, String>> holdingsReport = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).trim().equals("Holdings report")).collect(Collectors.toList());
        if (holdingsReport == null || holdingsReport.size() <= 2) {
            return new ArrayList<>(); // 如果列表为空或只有两个元素，直接返回空列表
        }
        List<Map<Integer, String>> subList = holdingsReport.subList(1, holdingsReport.size() - 1);
        List<Position> positions = new ArrayList<>();
        String portfolioValueOfBeginOfMonth = getPortfolioValueOfBeginOfMonth(reportDate);
        String portfolioValue = portfolioValueOfBeginOfMonth!=null?portfolioValueOfBeginOfMonth:getPortfolioValue(list);
        Position position ;
        for (Map<Integer, String> map : subList) {
            position = new Position();
            position.setTicker(map.get(5));
            SecurityInfo securityInfo = securityInfoMap.get(map.get(5));
            if (securityInfo != null){
                position.setSector(securityInfo.getSector());
            }
            position.setQuantity(map.get(12));
            position.setCost(map.get(14));
            position.setMarketPriceLocalCCY(map.get(11));
            String beginningPrice = beginningPriceService.getBeginningPrice(position.getTicker(), reportDate);
            if (beginningPrice == null){
                beginningPrice = map.get(14);
                if(canConvertToBigDecimal(beginningPrice)){
                    beginningPriceService.save(new BeginningPrice().setDate(reportDate).setPriceBeginningOfYear(beginningPrice).setStockCode(position.getTicker()));
                }
            }
            position.setPriceBeginningOfYear(beginningPrice);
            position.setMarketValueLocalCCY(map.get(13));
//            String unrealizedPnl = unrealizedPnlMap.get(position.getTicker());
//            if (new BigDecimal(unrealizedPnl).compareTo(BigDecimal.ZERO) == 0){
//                position.setUnrealizedPNLAsAPercentageOfCost("0");
//            }else {
//                BigDecimal percentage = new BigDecimal(unrealizedPnl).divide(new BigDecimal(position.getCost()).multiply(new BigDecimal(position.getQuantity())),4,RoundingMode.HALF_UP);
//                position.setUnrealizedPNLAsAPercentageOfCost(percentage.multiply(new BigDecimal("100")).setScale(2,RoundingMode.HALF_UP)+"%");
//            }

            //（持仓表E-持仓表D)/持仓表D
            String e = position.getMarketPriceLocalCCY();
            String d = position.getCost();
            if (!canConvertToBigDecimal(e) || !canConvertToBigDecimal(d)){
                position.setUnrealizedPNLAsAPercentageOfCost("0");
            }else {
                BigDecimal percentage = new BigDecimal(e).subtract(new BigDecimal(d)).divide(new BigDecimal(d),4,RoundingMode.HALF_UP);
                position.setUnrealizedPNLAsAPercentageOfCost(percentage.multiply(new BigDecimal("100")).setScale(2,RoundingMode.HALF_UP)+"%");
            }
            //TODO 持仓表H / 持仓表总敞口 (102行)
//            position.setExposureAsAPercentageOfGrossExposure("0");
            position.setUnderlierLocalCCY(map.get(8));
            String fx = getFx(list, map.get(8));
            position.setExchangeRate(fx);
            BigDecimal gm = new BigDecimal(position.getExchangeRate()).multiply(new BigDecimal(position.getMarketValueLocalCCY())).setScale(2, RoundingMode.HALF_UP);
            //G*M
            position.setMarketValueUSD(gm.toPlainString());

            //H / FUTU-VR-I (美元)
            BigDecimal value = gm.divide(new BigDecimal(portfolioValue), 4, RoundingMode.HALF_UP).multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP);
            position.setMarketValueUSDAsAPercentageOfBeginOfMonthNAV(value.toPlainString()+"%");
            position.setMarketValueUSDAsAPercentageOfBeginOfMonthNAVValue(value);
            positions.add(position);
        }
        return positions;
    }

    private List<Transaction> getTransactionList(List<Map<Integer,String>> list, Map<String, SecurityInfo> securityInfoMap){
        List<Map<Integer, String>> tradeReportList = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).equals("trade report")).collect(Collectors.toList());
        if (tradeReportList == null || tradeReportList.size() <= 2) {
            log.error("【getTransactionList】列表为空或只有两个元素，直接返回空列表");
            Transaction transaction = new Transaction();
            transaction.setTradeDate("For Delete");
            return Lists.newArrayList( transaction);
        }
        List<Map<Integer, String>> subList = tradeReportList.subList(1, tradeReportList.size() - 1);
        List<Transaction> transactionList = new ArrayList<>();
        Transaction transaction = null;
        Optional<Map<Integer, String>> baseCurrencyOptional =
                list.stream().filter(e -> e.get(0) != null && e.get(0).trim().equals("Base Currency")).findFirst();
        String baseCurrency = "";
        if (baseCurrencyOptional.isPresent()){
            baseCurrency = baseCurrencyOptional.get().get(1);
        }
        for (Map<Integer, String> map : subList) {
            transaction = new Transaction();
            transaction.setTradeDate(map.get(5));
            transaction.setSettlementDate(map.get(6));
            String buyOrSell = map.get(12);
            if ("Buy".equals(buyOrSell)){
                transaction.setBs("B");
            }else {
                transaction.setBs("S");
            }
            transaction.setMarket(map.get(8));
            transaction.setQty(map.get(14));
//            transaction.setGrossAmount(map.get(15));
            String netSettlementAmount = map.get(16);
            if(StringUtils.isEmpty(netSettlementAmount)){
               netSettlementAmount = "0";
            }else{
                netSettlementAmount = netSettlementAmount.replace(",","");
            }
            transaction.setNetSettlementAmount(netSettlementAmount);
            transaction.setStockCode(map.get(11));
            SecurityInfo securityInfo = securityInfoMap.get(transaction.getStockCode());
            if (securityInfo != null){
                transaction.setIsin(securityInfo.getIsin());
                transaction.setExchangeName(securityInfo.getExchangeName());
                transaction.setSecurityName(securityInfo.getSecurityName());
            }else {
                SecurityInfo info = EastMoneyApiCrawler.crawlStockInfo(transaction.getStockCode());
                if (info != null){
                    transaction.setIsin(info.getIsin());
                    transaction.setSecurityName(info.getSecurityName());
                    transaction.setExchangeName(securityInfo.getExchangeName());
                    securityInfoService.save( info);
                    securityInfoMap.put(transaction.getStockCode(),info);
                }
            }
            transaction.setName(map.get(7) );
            transaction.setSecurityName(map.get(7));
            transaction.setUnderlyingCcy(map.get(9));
            BigDecimal grossPriceLocalCCY = new BigDecimal(map.get(13));
            transaction.setGrossPriceLocalCCY(grossPriceLocalCCY.toString());
            BigDecimal R = new BigDecimal(map.get(17));
            BigDecimal S = new BigDecimal(map.get(18));
            BigDecimal T = new BigDecimal(map.get(19));
            //汇率*N
            String currency = map.get(9);
            if ("USD".equals(currency)){
                transaction.setFx("1.00");
            }else {
                transaction.setFx(getFx(list, currency));
            }

            transaction.setSettleCCY(baseCurrency);
            String fx = "1";
            if (!"USD".equals(baseCurrency)){
                fx = getFx(list,baseCurrency);
            }
            BigDecimal RST = R.add(S).add(T);
            if (!StringUtils.isBlank(fx)){
                BigDecimal grossPriceSettleCCY = new BigDecimal(fx).multiply(grossPriceLocalCCY);
                transaction.setGrossPriceSettleCCY(grossPriceSettleCCY.toString());
                BigDecimal grossAmountSettleCCY = grossPriceSettleCCY.multiply(new BigDecimal(transaction.getQty()));
                transaction.setGrossAmountSettleCCY(grossAmountSettleCCY.toString());
                BigDecimal netAmountSettleCCY = grossAmountSettleCCY.subtract(RST);
                transaction.setNetAmountSettleCCY(netAmountSettleCCY.toString());
                transaction.setNetPriceSettleCCY(netAmountSettleCCY.divide(new BigDecimal(transaction.getQty()),2,BigDecimal.ROUND_HALF_UP).toString());
            }

            transaction.setCounterpartyFeeCentsPerShare(RST.toString());
            transaction.setWithholdTaxSettlementCcy(T.toPlainString());
            //TODO 判断开仓平仓
            transaction.setOpenOrUnwind("");
            transaction.setMarginOrFFSOrPTH(map.get(4));
            transaction.setNetPriceLocalCCY(new BigDecimal(transaction.getFx()).multiply(new BigDecimal(transaction.getNetPriceSettleCCY())).toString());
            transactionList.add(transaction);
        }
        return transactionList;
    }
    public List<Equity> getEquityList(List<Map<Integer,String>> list,Map<String, SecurityInfo> securityInfoMap,Map<String, List<Transaction>> collect) throws ParseException {
        List<Map<Integer, String>> holdingsReport = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).trim().equals("Holdings report")).collect(Collectors.toList());
        if (holdingsReport == null || holdingsReport.size() <= 2) {
            return new ArrayList<>(); // 如果列表为空或只有两个元素，直接返回空列表
        }
        Optional<Map<Integer, String>> reportDateOPtional = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).trim().equals("Report Date")).findFirst();
        if (!reportDateOPtional.isPresent()){
            return new ArrayList<>();
        }
        log.info("【getEquityList】报告日期:{}",reportDateOPtional.get().get(1));
        String reportDateStr = reportDateOPtional.get().get(1);

        List<String> restList = restScheduleService.getRestList();
        Date reportDate = DateUtils.parseDate(reportDateStr, "yyyy/MM/dd");
        boolean rest = false;
        if (restList.contains(reportDateStr)){
            rest = true;
        }

        List<Map<Integer, String>> subList = holdingsReport.subList(1, holdingsReport.size() - 1);
        List<Equity> equityList = new ArrayList<>();
        Equity equity ;
        for (Map<Integer, String> map : subList) {
            equity = new Equity();
            equity.setDate(reportDate);
            equity.setMarket(map.get(7));
            equity.setTicker(map.get(5));
            equity.setInvestmentName(map.get(6));
            SecurityInfo securityInfo = securityInfoMap.get(equity.getTicker());
            if (securityInfo != null){
                equity.setExchangeName(securityInfo.getExchangeName());
            }
            equity.setQuantity(map.get(12));
            String unrealizedPnlValue = map.get(15);
            if (canConvertToBigDecimal(unrealizedPnlValue)){
                equity.setUnrealizedPnlTotal(unrealizedPnlValue);
            }else {
                equity.setUnrealizedPnlTotal("0");
            }
            String type = map.get(10);


            String quantity = equity.getQuantity();
            BigDecimal unrealizedPnl = BigDecimal.ZERO;
            List<DailyPnl> dailyPnls = dailyPnlService.listByMonth(equity.getTicker(),reportDate);
            if (quantity != null && canConvertToBigDecimal( quantity) && new BigDecimal(quantity).compareTo(BigDecimal.ZERO) == 0){
                equity.setMtdPNLUnrealised(unrealizedPnl.toPlainString());
                equity.setUnrealizedDailyPNLUSD("0");
                equity.setHolding(false);
            }else {
                unrealizedPnl = dailyPnls.stream().filter(e -> canConvertToBigDecimal(e.getUnrealizedPnl())&& e.getHolding())
                        .map(e -> new BigDecimal(e.getUnrealizedPnl()))
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                equity.setMtdPNLUnrealised(unrealizedPnl.toPlainString());
                equity.setHolding(true);
            }
            DailyPnl pnl = dailyPnlService.listLastHoldingOne(equity.getTicker(), reportDate);
            if (equity.getHolding() && canConvertToBigDecimal(unrealizedPnlValue)){
                if ( pnl != null){
                    equity.setUnrealizedDailyPNLUSD(new BigDecimal(unrealizedPnlValue).subtract(new BigDecimal(pnl.getUnrealizedPnlTotal())).toPlainString());
                }else{
                    equity.setUnrealizedDailyPNLUSD(unrealizedPnlValue);
                }
            }else {
                equity.setUnrealizedDailyPNLUSD("0");
            }
            if(collect.containsKey(equity.getTicker())){
                //
                List<Transaction> transactions = collect.get(equity.getTicker());
                //卖出总数量
                BigDecimal reduce = transactions.stream().filter(e -> canConvertToBigDecimal(e.getQty())).map(e -> new BigDecimal(e.getQty()))
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                //卖出总金额
                BigDecimal reduce1 = transactions.stream().filter(e -> canConvertToBigDecimal(e.getNetSettlementAmount())).map(e -> new BigDecimal(e.getNetSettlementAmount()))
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                List<TransactionDetail> detailList = transactionDetailService.list(equity.getTicker(), reportDate);
                BigDecimal temp = BigDecimal.ZERO;
                List<TransactionDetail> calList = new ArrayList<>();
                for (TransactionDetail detail : detailList) {
                    temp = temp.add(new BigDecimal(detail.getQty()));
                    calList.add(detail);
                    if (temp.compareTo(reduce) >= 0){
                        break;
                    }
                }
                //总的计算金额
                BigDecimal reduce2 = calList.stream().map(e -> new BigDecimal(e.getValue())).reduce(BigDecimal.ZERO, BigDecimal::add);
                //总的计算数量
                BigDecimal reduce3 = calList.stream().map(e -> new BigDecimal(e.getQty())).reduce(BigDecimal.ZERO, BigDecimal::add);
                //扣减的金额
                BigDecimal reduce4 = reduce2.multiply(reduce.divide(reduce3));

                equity.setRealizedDailyPNLUSD(reduce1.subtract(reduce4).toPlainString());
                //剩余数量的话，新增一个买入的明细
                if(reduce3.compareTo(reduce) > 0){
                    TransactionDetail transactionDetail = new TransactionDetail();
                    transactionDetail.setStockCode(equity.getTicker());
                    transactionDetail.setDate(reportDate);
                    transactionDetail.setOperation("B");
                    transactionDetail.setQty(reduce3.subtract(reduce).toPlainString());
                    transactionDetail.setValue(reduce2.subtract(reduce4).toPlainString());
                    transactionDetail.setRemaining(1    );
                    transactionDetailService.save(transactionDetail);
                }
                List<Integer> ids = calList.stream().map(e -> e.getId()).collect(Collectors.toList());
                transactionDetailService.tagCalculate(ids);
//                if ( pnl != null){
//                    equity.setRealizedDailyPNLUSD(new BigDecimal(map.get(16)).subtract(new BigDecimal(pnl.getRealizedPnl())).toPlainString());
//                }else{
//                    equity.setRealizedDailyPNLUSD(map.get(16));
//                }
                System.out.println(reportDateStr+"【产生了卖出行为】已实现损益为"+equity.getRealizedDailyPNLUSD()+"：卖出的票为"+equity.getTicker());
            }
            if("Fund".equals(type) && "Other Market".equals(equity.getMarket().trim())){
                equity.setRealizedDailyPNLUSD(map.get(16));
            }
            BigDecimal realizedPnl = dailyPnls.stream().filter(e -> canConvertToBigDecimal(e.getRealizedPnl()))
                    .map(e -> new BigDecimal(e.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            equity.setMtdPNLRealised(realizedPnl.toPlainString());
            if (canConvertToBigDecimal(equity.getRealizedDailyPNLUSD())){
                equity.setMtdPNLRealised(realizedPnl.add(new BigDecimal(equity.getRealizedDailyPNLUSD())).toPlainString());
            }


            List<DailyPnl> yearPnls = dailyPnlService.listByYear(equity.getTicker(),reportDate);
            BigDecimal yearUnrealizedPnl = yearPnls.stream().filter(e -> canConvertToBigDecimal(e.getUnrealizedPnl())&& e.getHolding())
                    .map(e -> new BigDecimal(e.getUnrealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal yearRealizedPnl = yearPnls.stream().filter(e -> canConvertToBigDecimal(e.getRealizedPnl()))
                    .map(e -> new BigDecimal(e.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
//            equity.setYtdPNL(yearUnrealizedPnl.add(yearRealizedPnl).toPlainString());
            List<DailyPnl> pnls = dailyPnlService.list(equity.getTicker(), reportDate);
//            BigDecimal itdPNL = pnls.stream().filter(e -> canConvertToBigDecimal(e.getUnrealizedPnl())&& e.getHolding())
//                    .map(e -> new BigDecimal(e.getUnrealizedPnl()))
//                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal itdPNL = new BigDecimal(equity.getUnrealizedPnlTotal());
            BigDecimal itdRealizedPNL = pnls.stream().filter(e -> canConvertToBigDecimal(e.getRealizedPnl()))
                    .map(e -> new BigDecimal(e.getRealizedPnl()))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            unrealizedPnlValue = equity.getUnrealizedDailyPNLUSD();
            if(!equity.getHolding()){
                yearUnrealizedPnl = BigDecimal.ZERO;
                unrealizedPnlValue = "0";
            }
//            equity.setItdPNL(itdPNL.add(itdRealizedPNL).toPlainString());
            equity.setMtdPNLUnrealised(unrealizedPnl.add(new BigDecimal(unrealizedPnlValue)).toPlainString());
            equity.setYtdPNL(yearUnrealizedPnl.add(yearRealizedPnl).add(new BigDecimal(unrealizedPnlValue)).add(new BigDecimal(equity.getRealizedDailyPNLUSD())).toPlainString());
            equity.setItdPNL(itdPNL.add(itdRealizedPNL).add(new BigDecimal(equity.getRealizedDailyPNLUSD())).toPlainString());
            equityList.add(equity);
        }
        return equityList;
    }
    private boolean canConvertToBigDecimal(String str) {
        try {
            new BigDecimal(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }
    public String getFx(List<Map<Integer,String>> list, String currency){
        List<Map<Integer, String>> exchangeRateReport = list.stream()
                .filter(e -> e.get(0) != null && e.get(0).equals("Exchange Rate Report")).collect(Collectors.toList());
        if (exchangeRateReport == null || exchangeRateReport.size() <= 1){
            return "1";
        }
        List<Map<Integer, String>> subList = exchangeRateReport.subList(1, exchangeRateReport.size());
        for (Map<Integer, String> map : subList) {
            if (map.get(2).startsWith(currency)){
                return map.get(3);
            }
        }
        return "1";
    }
    private List<Map<Integer,String>> readAttachmentsData() {
        String pathname = directory + "/attachment";
        File directory = new File(pathname);

        Optional<File> latestFile = Arrays.stream(directory.listFiles())
                .max(Comparator.comparing(File::getName));
        if (!latestFile.isPresent()){
            return new ArrayList<>();
        }
        File file = latestFile.get();
        InputStream in;
        try {
            in = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            log.error("【sendFundDailyEmailTask】任务结束:发送基金日报邮件失败......");
            log.error("文件不存在，文件路径attachmentFileName={}", file);
            throw new RuntimeException(e);
        }
        List<Map<Integer,String>> list = ExcelUtils.readExcel(in);
        return list;
    }

    private List<List<Map<Integer,String>>> readAttachmentsDataList() {
        String pathname = directory + "/test";
        File directory = new File(pathname);

//        Optional<File> latestFile = Arrays.stream(directory.listFiles())
//                .max(Comparator.comparing(File::getName));
//        if (!latestFile.isPresent()){
//            return new ArrayList<>();
//        }
        List<List<Map<Integer, String>>> collect = Arrays.stream(directory.listFiles())
                .sorted(Comparator.comparing(File::getName))
                .filter(file -> file.getName().startsWith("FUTU")).map(file -> {
//            File file = latestFile.get();
            InputStream in;
            try {
                in = new FileInputStream(file);
            } catch (FileNotFoundException e) {
                log.error("【sendFundDailyEmailTask】任务结束:发送基金日报邮件失败......");
                log.error("文件不存在，文件路径attachmentFileName={}", file);
                throw new RuntimeException(e);
            }
            List<Map<Integer, String>> list = ExcelUtils.readExcel(in);
            return list;
        }).collect(Collectors.toList());

        return collect;
    }

    private String getReportDate(Date reportDate) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String formattedDate = sdf.format(reportDate);
        return formattedDate;
    }
    private void downloadAttachments() {
        log.info("【downloadAttachments】开始任务:下载附件......");
        // 企业微信邮箱配置
        String host = "imap.exmail.qq.com"; // IMAP服务器
        String username = "shenghong@qidianqiuxue.com"; // 邮箱地址
        String password = "Qidian842"; // 密码或授权码

        // 下载附件的保存目录
        String saveDirectory = directory+"/attachment";

        try {
            WeChatWorkEmailAttachmentDownloader.downloadAttachments(host, username, password, saveDirectory);
            log.info("【downloadAttachments】任务结束:下载附件成功......");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        FundTestTask fundTask = new FundTestTask();
        System.out.println(fundTask.formatNumber("-123456789.123456789%"));
    }
    private void sendEmail(PortfolioInformation portfolioInformation,String attachmentPath){
        log.info("【sendEmailWithAttachments】开始任务:发送邮件......");
        // 邮件服务器配置
//        String host = "imap.exmail.qq.com"; // IMAP服务器
        String host = "smtp.exmail.qq.com"; // IMAP服务器
        String port = "465"; // 常用端口：25, 465(SSL), 587(TLS)
        // 发件人信息
        String from = "shenghong@qidianqiuxue.com";
        // 企业微信邮箱配置
        String username = "shenghong@qidianqiuxue.com"; // 邮箱地址
        String password = "Qidian842"; // 密码或授权码
        // 收件人信息
//        String to = "zhangli@righteous.hk;suwind@126.com;liuyitong@righteous.hk";
//        String to = "824095775@qq.com";
//        String to = "jiangsongling@qidianqiuxue.com,824095775@qq.com";
//        String to = "shenghong@qidianqiuxue.com";
//        String to = "824095775@qq.com";
        String to = "zhangli@righteous.hk,suwind@126.com,liuyitong@righteous.hk,shenghong@qidianqiuxue.com";

        // 邮件内容
        String subject = "Sheng Hong Daily Report "+DateUtil.formatDate("yyyyMMdd",new Date());
        String text = "【Portfolio Information】\n"+PortfolioFormatter.format(portfolioInformation);

        // 附件路径
        //String attachmentPath = "/Users/zhaolele/Documents/mail/FUTU_Daily_Report20250604.xlsx"; // 替换为实际文件路径

        // 发送邮件
        sendEmailWithAttachment(host, port, from, username, password, to, subject, text, attachmentPath);
    }

    private String formatNumber(String numberStr){
        if(numberStr == null){
            return numberStr;
        }
        if(!canConvertToBigDecimal(numberStr)){
            return numberStr;
        }
        BigDecimal number = new BigDecimal(numberStr);

        // 获取小数位数
        int scale = number.scale();

        // 动态构建格式模式
        String pattern = "#,##0";
        if (scale > 0) {
            pattern += "." + new String(new char[scale]).replace("\0", "0");
        }

        DecimalFormat df = new DecimalFormat(pattern);
        String formatted = df.format(number);
        return formatted;
    }



}
